package fic.storefinder.server.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

import fic.storefinder.shared.serializable.POI;
import fic.storefinder.shared.utils.Constants;

public class POITable extends Table {
	public long getMaxLastUpdateDate() throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		try {
			this.openConnection();
			
			
			stmt = this.connection.createStatement();
			rs = stmt.executeQuery("select max(LastUpdateDate) from POI;");
			Timestamp dbSqlTimestamp = null;
			while (rs.next()) {
				dbSqlTimestamp = rs.getTimestamp("max(LastUpdateDate)");
			}
			
			if (dbSqlTimestamp != null) {
				return dbSqlTimestamp.getTime();
			} else {
				return 0;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			this.closeConection();
		}
		

	}
	
	public ArrayList<HashMap<String, Object>> getPOIsToUpdate(long lastUpdateTimestamp) throws SQLException{
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "select POIId, POITitle, Address, City, ZipCode, Longitude, Latitude, Phone, POIStatus from POI where LastUpdateDate > ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setTimestamp(1, new Timestamp(lastUpdateTimestamp));
			rs = pstmt.executeQuery(queryStr);
			ArrayList<HashMap<String, Object>> retValue = new ArrayList<HashMap<String, Object>>();
			while (rs.next()) {
				HashMap<String, Object> rowMap = new HashMap<String, Object>();
				rowMap.put(Constants.JSON_KEY_POI_ID, rs.getObject(1));
				rowMap.put(Constants.JSON_KEY_POI_NAME, rs.getObject(2));
				rowMap.put(Constants.JSON_KEY_POI_ADDRESS, rs.getObject(3));
				rowMap.put(Constants.JSON_KEY_POI_CITY, rs.getObject(4));
				rowMap.put(Constants.JSON_KEY_POI_ZIPCODE, rs.getObject(5));
				rowMap.put(Constants.JSON_KEY_POI_LON, rs.getObject(6));
				rowMap.put(Constants.JSON_KEY_POI_LAT, rs.getObject(7));
				rowMap.put(Constants.JSON_KEY_POI_PHONE, rs.getObject(8));
				rowMap.put(Constants.JSON_KEY_POI_STATUS, rs.getObject(9));
				retValue.add(rowMap);
			}
			
			return retValue;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
		
	}
	
	public ArrayList<POI> getAllActivePOI() throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			ArrayList<POI> retValue = new ArrayList<POI>();

			
			
			String queryStr = "select POIId, POITitle, Address, City, ZipCode, Longitude, Latitude, Phone, POIStatus from POI where POIStatus = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, Constants.POISTATUS_ACTIVE);
			rs = pstmt.executeQuery(queryStr);
			while (rs.next()) {
				POI poi = new POI();
				poi.setPoiId(rs.getInt(1));
				poi.setPoiTitle(rs.getString(2));
				poi.setAddress(rs.getString(3));
				poi.setCity(rs.getString(4));
				poi.setZipCode(rs.getString(5));
				poi.setLongitude(rs.getFloat(6));
				poi.setLatitude(rs.getFloat(7));
				poi.setPhone(rs.getString(8));
				poi.setPoiStatus(rs.getInt(9));
				retValue.add(poi);
			}
			
			
			return retValue;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
		
		
	}
	
	public POI getPOIById (int poiId) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			POI retValue = new POI();
			
			String queryStr = "select POIId, POITitle, Address, City, ZipCode, Longitude, Latitude, Phone, POIStatus from POI where POIId = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, poiId);
			rs = pstmt.executeQuery(queryStr);
			while (rs.next()) {
				retValue.setPoiId(rs.getInt(1));
				retValue.setPoiTitle(rs.getString(2));
				retValue.setAddress(rs.getString(3));
				retValue.setCity(rs.getString(4));
				retValue.setZipCode(rs.getString(5));
				retValue.setLongitude(rs.getFloat(6));
				retValue.setLatitude(rs.getFloat(7));
				retValue.setPhone(rs.getString(8));
				retValue.setPoiStatus(rs.getInt(9));
			}
			
			return retValue;
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
		
	}
	
	public void updatePoi(POI poi) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "UPDATE POI SET POITitle = ?, Address = ?, City = ?, ZipCode = ?, Longitude = ?, Latitude = ?, Phone = ?, LastUpdateDate = ?  WHERE POIId = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setString(1, poi.getPoiTitle());
			pstmt.setString(2, poi.getAddress());
			pstmt.setString(3, poi.getCity());
			pstmt.setString(4, poi.getZipCode());
			pstmt.setFloat(5, poi.getLongitude());
			pstmt.setFloat(6, poi.getLatitude());
			pstmt.setString(7, poi.getPhone());
			pstmt.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(9, poi.getPoiId());
			pstmt.executeUpdate(queryStr);
		
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}

	public void insertPOI(POI poi) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "INSERT INTO POI (POITitle, Address, City, ZipCode, Longitude, Latitude, Phone, LastUpdateDate, POIStatus) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setString(1, poi.getPoiTitle());
			pstmt.setString(2, poi.getAddress());
			pstmt.setString(3, poi.getCity());
			pstmt.setString(4, poi.getZipCode());
			pstmt.setFloat(5, poi.getLongitude());
			pstmt.setFloat(6, poi.getLatitude());
			pstmt.setString(7, poi.getPhone());
			pstmt.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(9, Constants.POISTATUS_ACTIVE);
			pstmt.executeUpdate(queryStr);
		
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}

	public void deletePOI(POI poi) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "UPDATE POI SET POIStatus = ?, LastUpdateDate = ? WHERE POIId = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, Constants.POISTATUS_DELETED);
			pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(3, poi.getPoiId());
			pstmt.executeUpdate(queryStr);
			
			//mark deleted all robe for this store:
			RobeTable robeTable = new RobeTable();
			robeTable.deleteAllRobesForPOI(poi.getPoiId());
			
			
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}
}
